package com.kingdee.eas.custom.tiog.report.app;

import org.apache.log4j.Logger;
import com.kingdee.bos.*;
import java.lang.String;
import java.sql.SQLException;
import com.kingdee.bos.db.TempTablePool;
import com.kingdee.eas.basedata.org.AdminOrgUnitInfo;
import com.kingdee.eas.common.EASBizException;
import com.kingdee.eas.framework.report.util.DBUtil;
import com.kingdee.eas.framework.report.util.RptParams;
import com.kingdee.eas.framework.report.util.RptRowSet;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.util.NumericExceptionSubItem;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

public class CatalogLifecycleAnalyzeFacadeControllerBean extends AbstractCatalogLifecycleAnalyzeFacadeControllerBean
{
    private static Logger logger =
        Logger.getLogger("com.kingdee.eas.custom.tiog.report.app.CatalogLifecycleAnalyzeFacadeControllerBean");
    private SimpleDateFormat sdft = new SimpleDateFormat("yyyy-MM-dd");
    int index4List = 0;
    int index_Insert_SEQ = 0;
    ArrayList<String> tempTable_Columns = new ArrayList<String>();
    
    protected RptParams _createTempTable(Context ctx, RptParams params) throws BOSException, EASBizException {
        String creatSql = str_CreateTempTable(ctx, params);
        try {
            String tableName = params.getString("tempName");
            try {
                tableName = TempTablePool.getInstance(ctx).createTempTable(creatSql.toString());
            } catch (Exception e) {
                e.printStackTrace();
            }
            filledWithData(ctx, params, tableName);
            params.setInt("count", getTableCount(ctx, tableName));
            params.setString("tempName", tableName);
            params.setObject("tempTable_Columns", tempTable_Columns);
            return params;
        } catch (Exception e) {
            throw new EASBizException(new NumericExceptionSubItem(e.getMessage(),e.getMessage()));
        }
    }
    //创建临时表
    private String str_CreateTempTable(Context ctx, RptParams params){
        AdminOrgUnitInfo adminOrg = (AdminOrgUnitInfo)params.getObject("prmtDept");
        StringBuffer sql =new StringBuffer();
        String str_Feilds = "";
        String[] states = new String[]{"CatalogMain", "CategoryList", "PoliticalSystem", "Subject", "MeetingMan", "Execution"};
        sql.append(" create table Temp_FixedAssetsHQ (")
            .append(" SQLSEQ INT NOT NULL, ");
        tempTable_Columns.add("SQLSEQ");
        for(int i = 0, count = states.length; i < count; i++){
            sql.append("ID_" + states[i] + " VARCHAR(100), ");
            tempTable_Columns.add("ID_" + states[i]);
            sql.append("Number_" + states[i] + " VARCHAR(50), ");
            tempTable_Columns.add("Number_" + states[i]);
            sql.append("Name_" + states[i] + " VARCHAR(1000), ");
            tempTable_Columns.add("Name_" + states[i]);
            sql.append("Date_" + states[i] + " DATETIME, ");
            tempTable_Columns.add("Date_" + states[i]);
            //事项里没有状态
/*            if(!"CatalogMain".equals(states[i])){
            }
*/            
            //到头，不能有", "
            if("Execution".equals(states[i])){
                sql.append("State_" + states[i] + " VARCHAR(50)");
                tempTable_Columns.add("State_" + states[i]);
            } else {
                sql.append("State_" + states[i] + " VARCHAR(50), ");
                tempTable_Columns.add("State_" + states[i]);
            }
        }
        sql.append(" )");
        return sql.toString();
    }
        
        
    /**
     * 
     * @param ctx
     * @param params
     * @param tableName
     * @throws BOSException
     */
    private void filledWithData(Context ctx, RptParams params,String tableName) throws BOSException {
        //AdminOrgUnitInfo adminOrg = (AdminOrgUnitInfo)params.getObject("prmtDept");
        Date start = (Date)params.getObject("start");
        Date end = (Date)params.getObject("end");
        int pop = 1;
        StringBuffer sql = new StringBuffer("select ")
            .append("CatalogMain.FID ").append(tempTable_Columns.get(pop++))
            .append(", CatalogMain.FNumber ").append(tempTable_Columns.get(pop++))
            .append(", CatalogMain.FName_L2 ").append(tempTable_Columns.get(pop++))
            .append(", CatalogMain.FLastUpdateTime ").append(tempTable_Columns.get(pop++))
            .append(",  CatalogStatus.FName_L2 ").append(tempTable_Columns.get(pop++))
            //--事项清单内容：编码、名称、日期
            .append(", CategoryList.FID ").append(tempTable_Columns.get(pop++))
            .append(", CategoryList.FNumber ").append(tempTable_Columns.get(pop++))
            .append(", CategoryList.CFCategoryListName ").append(tempTable_Columns.get(pop++))
            .append(", CategoryList.FBizDate ").append(tempTable_Columns.get(pop++))
            .append(", CategoryList.CFState ").append(tempTable_Columns.get(pop++))
            //--决策制度内容：编码、名称、日期
            .append(", PoliticalSystem.FID ").append(tempTable_Columns.get(pop++))
            .append(", PoliticalSystem.FNumber ").append(tempTable_Columns.get(pop++))
            .append(", PoliticalSystem.CFPoliticalSystemName ").append(tempTable_Columns.get(pop++))
            .append(", PoliticalSystem.FBizDate ").append(tempTable_Columns.get(pop++))
            .append(", PoliticalSystem.CFState ").append(tempTable_Columns.get(pop++))
            //--审议议题内容：编码、名称、日期
            .append(", Subject.FID ").append(tempTable_Columns.get(pop++))
            .append(", Subject.FNumber ").append(tempTable_Columns.get(pop++))
            .append(", Subject.CFSubjectName ").append(tempTable_Columns.get(pop++))
            .append(", Subject.FBizDate ").append(tempTable_Columns.get(pop++))
            .append(", Subject.CFState ").append(tempTable_Columns.get(pop++))
            //--会议决策内容：编码、名称、日期
            .append(", MeetingMan.FID ").append(tempTable_Columns.get(pop++))
            .append(", MeetingMan.FNumber ").append(tempTable_Columns.get(pop++))
            .append(", MeetingMan.CFMeetingName ").append(tempTable_Columns.get(pop++))
            .append(", MeetingMan.FBizDate ").append(tempTable_Columns.get(pop++))
            .append(", MeetingMan.CFState ").append(tempTable_Columns.get(pop++))
            //--组织实施内容：编码、名称、日期
            .append(", Execution.FID ").append(tempTable_Columns.get(pop++))
            .append(", Execution.FNumber ").append(tempTable_Columns.get(pop++))
            .append(", Execution.FDescription ").append(tempTable_Columns.get(pop++))
            .append(", Execution.FBizDate ").append(tempTable_Columns.get(pop++))
            .append(", Execution.CFState ").append(tempTable_Columns.get(pop++))   
            .append(" from CT_BD_CatalogMain CatalogMain")
            .append(" left join CT_BD_CatalogStatus CatalogStatus on CatalogStatus.FID = CatalogMain.CFStatusID")
            .append(" left join CT_BZB_CategoryListEntry CategoryListEntry on CategoryListEntry.CFCatalogMainID = CatalogMain.FID")
            .append(" left join CT_BZB_CategoryList CategoryList on CategoryList.FID = CategoryListEntry.FParentID")
            .append(" left join CT_BZB_PoliticalSystemEntry PoliticalSystemEntry on PoliticalSystemEntry.CFCatalogMainID = CatalogMain.FID")
            .append(" left join CT_BZB_PoliticalSystem PoliticalSystem on PoliticalSystem.FID = PoliticalSystemEntry.FParentID")
            .append(" left join CT_BZB_SubjectCatalogEntry SubjectCatalogEntry on SubjectCatalogEntry.CFCatalogMainID = CatalogMain.FID")
            .append(" left join CT_BZB_Subject Subject on Subject.FID = SubjectCatalogEntry.FParentID")
            .append(" left join CT_BZB_MeetingManSubjectEntry MeetingManSubjectEntry on MeetingManSubjectEntry.CFSubjectID = Subject.FID")
            .append(" left join CT_BZB_MeetingMan MeetingMan on MeetingMan.FID = MeetingManSubjectEntry.FParentID")
            .append(" left join CT_BZB_Execution Execution on Execution.CFSubjectID = Subject.FID");
        sql.append(" where CatalogMain.FID is not null ");
        if(null != start && null != end){
            sql.append("and CatalogMain.FLastUpdateTime >= ").append(start).append(" and CatalogMain.FLastUpdateTime <= ").append(end);
        } else if(null != start && null == end){
            sql.append("and CatalogMain.FLastUpdateTime >= ").append(start);
        } else if(null == start && null != end){
            sql.append("and CatalogMain.FLastUpdateTime <= ").append(end);
        } else {
            
        }
        sql.append(" order by CatalogMain.FLastUpdateTime desc ");
        try {
            IRowSet rs = DbUtil.executeQuery(ctx, sql.toString());
            while(rs.next()){
                String insert = " insert into " + tableName + " values(";
                insert = insert + index_Insert_SEQ + getRSJointStr(rs) + " )";
                DbUtil.execute(ctx, insert);
                index_Insert_SEQ = index_Insert_SEQ + 1;
            }
        } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        } catch (BOSException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
        }
        
    }
        
    private String getRSJointStr(IRowSet rs) throws SQLException {
        // TODO Auto-generated method stub
        String returnStr = "";
        for(String column : tempTable_Columns){
            if("SQLSEQ".equals(column))
                continue;
            if(null != rs.getObject(column)){
                if(column.indexOf("Date") < 0)
                    returnStr = returnStr + ", '" + rs.getString(column) + "'";
                else
                    returnStr = returnStr + ", {ts'" + rs.getDate(column) + "'}";
            } else {
                returnStr = returnStr + ", null";
            }
        }
        return returnStr;
    }
    
        private int getTableCount(Context ctx, String tableName) {
        StringBuffer sql=new StringBuffer();
        sql.append("select count(*)").append(" from ").append(tableName);
                RptRowSet rs = null;
                try {
                        rs = DBUtil.executeQuery(sql.toString(), this.getConnection(ctx));
                } catch (Exception e) {
                        e.printStackTrace();
                }
                if(rs!=null && rs.next())
                        return rs.getInt(0);
                return 0;
        }
        
    protected RptParams _init(Context ctx, RptParams params) throws BOSException, EASBizException {
        RptParams pp = new RptParams();
        return pp;
    }

    protected RptParams _query(Context ctx, RptParams params, int from, int len) throws BOSException, EASBizException {
        String tableName = params.getString("tempName");
        String sql = "select * from " + tableName + " order by SQLSEQ asc";
        try {
            RptRowSet rs = DBUtil.executeQuery(sql.toString(), null, from, len, this.getConnection(ctx));
            params.setObject("rowset", rs);
            params.setInt("count", rs.getRowCount());
        } catch (SQLException e) {
            throw new EASBizException(new NumericExceptionSubItem(e.getMessage(),e.getMessage()));
        }
        return params;
    }
 }